﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using MySql.Data.MySqlClient;

namespace SQLTask2
{
    public partial class BanksForm : Form
    {
        private MySqlConnection pConn;
        private DataTable Countries = null;
        private DataTable Banks = null;
        private int parent_id;
        private Form1 form;
        private bool isEdit;
        private Bank bankEdited;

        struct Bank
        {
            public int Bank_ID;
            public string Name;
            public string Location;
            public int Parent_Bank_ID;
            public int Country_ID;
        }


        private string[] queries = {//0
                                    @"SELECT Bank_ID,
                                             Name
                                     FROM banks ORDER BY Bank_ID;",
                                    //1
                                    @"SELECT Country_ID,
                                             Name
                                     FROM countries ORDER BY Country_ID;",
                                    @"INSERT INTO countries (Name) VALUE ('{0}');",
                                    @"INSERT INTO banks (Name, Location, Parent_Bank_ID, Country_ID)
                                      VALUE ('{0}','{1}','{2}','{3}');",
                                    @"SELECT * FROM banks WHERE Bank_ID='{0}'",
                                    @"UPDATE banks SET Name='{0}', Location='{1}', Parent_Bank_ID='{2}',
                                                    Country_ID='{3}' WHERE Bank_ID='{4}';"
                                   };



        public BanksForm(Form1 frm, MySqlConnection conn, bool edit, int parentID = 0)
        {
            InitializeComponent();
            pConn = conn;
            parent_id = parentID;
            form = frm;
            isEdit = edit;
        }



        private void BanksForm_Load(object sender, EventArgs e)
        {
            try
            {
                if(isEdit)
                {
                    using (MySqlCommand cmd = new MySqlCommand(String.Format(queries[4], parent_id), pConn))
                    {
                        using (MySqlDataReader reader = cmd.ExecuteReader())
                        {
                            reader.Read();
                            bankEdited = new Bank { Bank_ID = (int)reader[0], Name = (string)reader[1], Location = (string)reader[2], Parent_Bank_ID = (int)reader[3], Country_ID = (int)reader[4] };
                        }
                    }

                    textBoxBankName.Text = bankEdited.Name;
                    textBoxBankLocation.Text = bankEdited.Location;
                }

                Banks = new DataTable();
                Countries = new DataTable();
                (new MySqlDataAdapter(queries[0], pConn)).Fill(Banks);
                comboBox2.DisplayMember = "Name";
                comboBox2.ValueMember = "Bank_ID";
                comboBox2.DataSource = Banks;
                checkBox1.Checked = isEdit?(bankEdited.Parent_Bank_ID != 0):(parent_id != 0);
                (new MySqlDataAdapter(queries[1], pConn)).Fill(Countries);
                comboBox1.DataSource = Countries; 
                comboBox1.DisplayMember = "Name";
                comboBox1.ValueMember = "Country_ID";
                if (isEdit)
                {
                    comboBox1.SelectedValue = bankEdited.Country_ID;
                    comboBox2.SelectedValue = bankEdited.Parent_Bank_ID;
                }
            }
            catch (MySqlException sqlExc)
            {
                MessageBox.Show(sqlExc.Message);
            }
        }

        private void button1_Click(object sender, EventArgs e)
        {
            try
            {
                int countryID = (int)comboBox1.SelectedValue;
                if (comboBox1.SelectedValue == null)
                {
                    using (MySqlCommand cmd = new MySqlCommand(String.Format(queries[2],comboBox1.Text), pConn))
                    {
                       int k = cmd.ExecuteNonQuery();
                    }
                    using (MySqlCommand cmd = new MySqlCommand("SELECT last_insert_id()", pConn))
                    {
                        countryID = Convert.ToInt32(cmd.ExecuteScalar());
                    }
                }
                string currQuery;
                if (isEdit)
                {
                    currQuery = String.Format(queries[5], textBoxBankName.Text,
                                                          textBoxBankLocation.Text,
                                                          comboBox2.SelectedValue,
                                                          countryID,
                                                          bankEdited.Bank_ID);
                }
                else
                {
                    currQuery = String.Format(queries[3], textBoxBankName.Text,
                                                          textBoxBankLocation.Text,
                                                          parent_id,
                                                          countryID);
                }
                using (MySqlCommand cmd = new MySqlCommand(currQuery, pConn))
                {
                        cmd.ExecuteNonQuery();
                }
                form.RefreshTreeView();
                this.Close();
            }
            catch (MySqlException exc)
            {
                MessageBox.Show(exc.Message);
            }
        }

        private void checkBox1_CheckedChanged(object sender, EventArgs e)
        {
            comboBox2.Enabled = checkBox1.Checked;
            if (checkBox1.Checked)
            {
                comboBox2.SelectedValue = parent_id;
            }
        }
    }
}
